	[ 7-1]
CREATE TABLE  (
	̵  VARCHAR(20)	 NOT NULL,
	̸     VARCHAR(10)	 NOT NULL,
		    INT,
		    VARCHAR(10)	 NOT NULL,
		    VARCHAR(20),
		    INT   DEFAULT 0,
	PRIMARY KEY(̵)
);


	[ 7-2]
CREATE TABLE  ǰ (
	ǰȣ   CHAR(3)   NOT NULL,
	ǰ      VARCHAR(20),
	      INT,
	ܰ         INT,
	ü    VARCHAR(20),
	PRIMARY KEY(ǰȣ),
	CHECK ( >= 0 AND  <=10000)
);


	[ 7-3]
CREATE TABLE  ֹ (
	ֹȣ   CHAR(3)  NOT NULL,
	ֹ   VARCHAR(20),
	ֹǰ   CHAR(3),
	         INT,
	      VARCHAR(30),
	ֹ   DATE,
	PRIMARY KEY(ֹȣ),
	FOREIGN KEY(ֹ)   REFERENCES   (̵),
	FOREIGN KEY(ֹǰ)   REFERENCES   ǰ(ǰȣ)
);


	[ 7-4]
CREATE TABLE  ۾ü (
	üȣ   CHAR(3)  NOT NULL,
	ü      VARCHAR(20),
	ּ         VARCHAR(100),
	ȭȣ   VARCHAR(20),
	PRIMARY KEY(üȣ)
);


	[ 7-5]
ALTER TABLE  ADD Գ¥ DATE; 


	[ 7-6]
ALTER TABLE  DROP COLUMN Գ¥;


	[ 7-7]
ALTER TABLE  ADD CONSTRAINT CHK_AGE CHECK( >= 20); 


	[ 7-8]
ALTER TABLE  DROP CONSTRAINT CHK_AGE; 


	[ 7-9]
DROP TABLE ۾ü; 


	[ 7-10 ]
[ ̺  ]
INSERT INTO  VALUES ('apple', 'ȭ', 20, 'gold', 'л', 1000);
INSERT INTO  VALUES ('banana', '輱', 25, 'vip', 'ȣ', 2500);
INSERT INTO  VALUES ('carrot', '', 28, 'gold', '', 4500);
INSERT INTO  VALUES ('orange', '', 22, 'silver', 'л', 0);
INSERT INTO  VALUES ('melon', '', 35, 'gold', 'ȸ', 5000);
INSERT INTO  VALUES ('peach', '', NULL, 'silver', 'ǻ', 300);
INSERT INTO  VALUES ('pear', 'ä', 31, 'silver', 'ȸ', 500);

[ǰ ̺  ]
INSERT INTO ǰ VALUES ('p01', '׳ɸ', 5000, 4500, 'ѽǰ');
INSERT INTO ǰ VALUES ('p02', 'ſ̸', 2500, 5500, 'αǪ');
INSERT INTO ǰ VALUES ('p03', '', 3600, 2600, 'Ѻ');
INSERT INTO ǰ VALUES ('p04', 'ݸ', 1250, 2500, 'Ѻ');
INSERT INTO ǰ VALUES ('p05', 'ū', 2200, 1200, 'ѽǰ');
INSERT INTO ǰ VALUES ('p06', '쵿', 1000, 1550, 'αǪ');
INSERT INTO ǰ VALUES ('p07', '޺Ŷ', 1650, 1500, 'Ѻ');

[ֹ ̺  ]
[  : ¥  ϱ  Ŭ to_date Լ , y , m , d ڸ Ÿ]
INSERT INTO ֹ VALUES ('o01', 'apple', 'p03', 10, ' ', to_date('22/01/01', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o02', 'melon', 'p01', 5, 'õ 籸', to_date('22/01/10', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o03', 'banana', 'p06', 45, '⵵ õ', to_date('22/01/11', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o04', 'carrot', 'p02', 8, 'λ ', to_date('22/02/01', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o05', 'melon', 'p06', 36, '⵵ ν', to_date('22/02/20', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o06', 'banana', 'p01', 19, 'ûϵ ', to_date('22/03/02', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o07', 'apple', 'p03', 22, ' ', to_date('22/03/15', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o08', 'pear', 'p02', 50, ' õ', to_date('22/04/10', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o09', 'banana', 'p04', 15, '󳲵 ', to_date('22/04/11', 'yy/mm/dd'));
INSERT INTO ֹ VALUES ('o10', 'carrot', 'p03', 20, '⵵ Ⱦ', to_date('22/05/22', 'yy/mm/dd'));


	[ 7-10]
SELECT	 ̵, ̸, 
FROM	 ;


	[ 7-11]
SELECT	 ̵, ̸, , , , 
FROM	 ;


	[ 7-12]
SELECT	 *
FROM	 ;


	[ 7-13]
SELECT	 ü
FROM	 ǰ;


	[ 7-14]
SELECT   ALL   ü
FROM    ǰ;


	[ 7-15]
SELECT    DISTINCT   ü
FROM     ǰ;


	[ 7-16]
SELECT	 ǰ, ܰ AS 
FROM	 ǰ;


	[ 7-17]_Ŭ
SELECT	 ǰ, ܰ + 500 AS " ܰ"
FROM	 ǰ;

	[ 7-17]_MS SQL
SELECT	 ǰ, ܰ + 500 AS ' ܰ'
FROM	 ǰ;


	[ 7-18]
SELECT	 ǰ, , ܰ
FROM	 ǰ
WHERE	 ü = 'Ѻ'; 


	[ 7-19]
SELECT	 ֹǰ, , ֹ
FROM	 ֹ
WHERE	 ֹ = 'apple'  AND   >= 15;


	[ 7-20]
SELECT	 ֹǰ, , ֹ, ֹ
FROM	 ֹ
WHERE	 ֹ = 'apple'  OR   >= 15;


	[ 7-21]
SELECT	 ǰ, ܰ, ü
FROM	 ǰ
WHERE	 ܰ >= 2000  AND  ܰ <= 3000;


	[ 7-22]
SELECT	 ̸, , , 
FROM	 
WHERE	 ̸ LIKE '%';


	[ 7-23]
SELECT	 ̵, ̸, 
FROM	 
WHERE	 ̵ LIKE '_____';


	[ 7-24]
SELECT	 ̸
FROM	 
WHERE	  IS NULL;


	[ 7-25]
SELECT	 ̸
FROM	 
WHERE	  IS NOT NULL;


	[ 7-26]
SELECT	 ̸, , 
FROM	 
ORDER BY     DESC;


	[ 7-27]
SELECT   ֹ, ֹǰ, , ֹ
FROM    ֹ
WHERE    >= 10
ORDER BY  ֹǰ ASC,  DESC;


	[ 7-28]
SELECT   AVG(ܰ)
FROM    ǰ;


	[ 7-29]
SELECT   SUM()  AS  " հ"
FROM    ǰ
WHERE   ü = 'Ѻ';


	[ 7-30]
SELECT   COUNT(̵) AS 
FROM    ;

SELECT   COUNT() AS 
FROM    ;

SELECT   COUNT(*) AS 
FROM    ;


	[ 7-31]
SELECT   COUNT(DISTINCT ü) AS "ü "
FROM    ǰ;


	[ 7-32]
SELECT   ֹǰ, SUM() AS ֹ
FROM    ֹ
GROUP BY   ֹǰ;

SELECT   SUM() AS ֹ
FROM    ֹ
GROUP BY   ֹǰ;


	[ 7-33]
SELECT   ü, COUNT(*) AS ǰ, MAX(ܰ) AS ְ
FROM    ǰ
GROUP BY   ü;


	[ 7-34]
SELECT   ü, COUNT(*) AS ǰ, MAX(ܰ) AS ְ
FROM    ǰ
GROUP BY  ü  HAVING COUNT(*) >= 3;


	[ 7-35]
SELECT   , COUNT(*) AS , AVG() AS 
FROM    
GROUP BY    HAVING AVG() >= 1000;


	[ 7-36]
SELECT   ֹǰ, ֹ, SUM() AS ֹ
FROM    ֹ
GROUP BY  ֹǰ, ֹ; 


	[ 7-37]
SELECT   ǰ.ǰ
FROM    ǰ, ֹ
WHERE   ֹ.ֹ = 'banana'  AND  ǰ.ǰȣ = ֹ.ֹǰ; 


	[ 7-38]
SELECT   ֹ.ֹǰ, ֹ.ֹ
FROM    , ֹ
WHERE   . >= 30  AND  .̵ = ֹ.ֹ; 

SELECT   o.ֹǰ, o.ֹ
FROM      c, ֹ o
WHERE   c. >= 30  AND  c.̵ = o.ֹ; 

SELECT   ֹ.ֹǰ, ֹ.ֹ
FROM      INNER JOIN  ֹ  ON  .̵ = ֹ.ֹ
WHERE   . >= 30;


	[ 7-39]
SELECT	 ǰ.ǰ
FROM	 , ǰ, ֹ
WHERE	 .̸ = ''   AND  .̵ = ֹ.ֹ 
		AND  ǰ.ǰȣ = ֹ.ֹǰ; 


[ܺ  ]
SELECT	 .̸, ֹ.ֹǰ, ֹ.ֹ
FROM	   LEFT OUTER JOIN  ֹ  ON  .̵ = ֹ.ֹ;

SELECT	 .̸, ֹ.ֹǰ, ֹ.ֹ
FROM	 ֹ  RIGHT OUTER JOIN    ON  ֹ.ֹ = .̵;


	[ 7-40]
SELECT	 ǰ, ܰ
FROM	 ǰ
WHERE	 ü = (SELECT   ü
                    	     FROM    ǰ
                    	     WHERE  ǰ = '޺Ŷ');

SELECT	 ǰ, ܰ
FROM	 ǰ
WHERE	 ü = 'Ѻ';


	[ 7-41]
SELECT	 ̸, 
FROM	 
WHERE	  = (SELECT  MAX()  FROM  ); 

SELECT	 ̸, 
FROM	 
WHERE	  = 5000; 


	[ 7-42]
SELECT	 ǰ, ü
FROM	 ǰ
WHERE	 ǰȣ  IN (SELECT  ֹǰ   
                                FROM   ֹ
                                WHERE  ֹ  = 'banana'); 

SELECT	 ǰ, ü
FROM	 ǰ
WHERE	 ǰȣ  IN  ('p01', 'p04', 'p06'); 


[ Ǹ ̿ SELECT ]
SELECT	 ǰ.ǰ, ǰ.ü
FROM	 ǰ, ֹ
WHERE	 ǰ.ǰȣ = ֹ.ֹǰ  AND  ֹ.ֹ  = 'banana'; 


[EXISTS ڸ ̿ SELECT ]
SELECT	 ǰ, ü
FROM	 ǰ
WHERE	 EXISTS  (SELECT  *   
                         FROM   ֹ
                         WHERE  ǰ.ǰȣ = ֹ.ֹǰ  
		             AND  ֹ.ֹ  = 'banana'); 


	[ 7-43]
SELECT	 ǰ, ü
FROM	 ǰ
WHERE	 ǰȣ  NOT  IN (SELECT	  ֹǰ   
                                        FROM	  ֹ
                                        WHERE	  ֹ  = 'banana'); 


	[ 7-44]
SELECT	 ǰ, ܰ, ü
FROM	 ǰ
WHERE	 ܰ > ALL (SELECT    ܰ   
                              FROM     ǰ
                              WHERE    ü  = 'ѽǰ'); 


	[ 7-45]
[  : ¥  ϱ  Ŭ to_date Լ , y , m , d ڸ Ÿ]
SELECT	 ̸
FROM	 
WHERE	 EXISTS  (SELECT  *   
		FROM    ֹ
		WHERE   ֹ = to_date('2022-03-15', 'yyyy-mm-dd') 
		        AND  ֹ.ֹ = .̵); 


	[ 7-46]
SELECT	 ̸
FROM	 
WHERE	 NOT EXISTS  (SELECT  *   
		       FROM    ֹ
		       WHERE   ֹ = to_date('2022-03-15', 'yyyy-mm-dd') 
		                    AND  ֹ.ֹ = .̵); 


	[ 7-47]
INSERT  
INTO   (̵, ̸, , , , )
VALUES ('strawberry', '', 30, 'vip', '', 100);

SELECT  *  FROM  ;  


	[ 7-48]
INSERT  
INTO    (̵, ̸, , , )
VALUES ('tomato', '', 36, 'gold', 4000);

SELECT  *  FROM  ;  


[μ ǹ ̿  ]
CREATE TABLE  Ѻǰ (
	ǰ      VARCHAR(20),
	      INT,
	ܰ         INT,
	PRIMARY KEY(ǰ)
);

INSERT  
INTO	 Ѻǰ(ǰ, , ܰ)
SELECT	 ǰ, , ܰ
FROM	 ǰ
WHERE	 ü = 'Ѻ';

SELECT  *  FROM  Ѻǰ;  


	[ 7-49]
UPDATE  ǰ  
SET        ǰ = 'ū'
WHERE   ǰȣ = 'p03';

SELECT  *  FROM  ǰ;  


	[ 7-50]
UPDATE  ǰ  
SET        ܰ = ܰ * 1.1;

SELECT  *  FROM  ǰ;  


	[ 7-51]
UPDATE  ֹ  
SET         = 5
WHERE   ֹ  IN  (SELECT      ̵
 		      FROM       
		      WHERE     ̸ = 'ȭ');

SELECT  *  FROM  ֹ; 


	[ 7-52]
[  : ¥  ϱ  Ŭ to_date Լ , y , m , d ڸ Ÿ]
DELETE
FROM    ֹ  
WHERE   ֹ = to_date('2022-05-22', 'yyyy-mm-dd');

SELECT  *  FROM  ֹ; 


	[ 7-53]
DELETE     
FROM    ֹ
WHERE   ֹ  IN  (SELECT   ̵
 	                   FROM    
		       WHERE   ̸ = 'ȭ');

SELECT  *  FROM  ֹ;  


	[ 7-54]
DELETE
FROM	 ֹ;

SELECT  *  FROM  ֹ; 


	[ 7-55]
CREATE VIEW     (̵, ̸, , )
AS SELECT         ̵, ̸, , 
     FROM          
     WHERE          = 'vip'
WITH CHECK OPTION;

SELECT  *  FROM    ;


CREATE VIEW       
AS SELECT           ̵, ̸, , 
     FROM            
     WHERE            = 'vip'
WITH CHECK OPTION;


	[ 7-56]
CREATE  VIEW	 üǰ(ü, ǰ)
AS  SELECT	 ü, COUNT(*)
      FROM	 ǰ
      GROUP BY   ü
WITH CHECK OPTION;

SELECT  *  FROM	 üǰ;


	[ 7-57]
SELECT  *  FROM    WHERE   >= 20;


CREATE VIEW	 ǰ1
AS SELECT	 ǰȣ, , ü
     FROM	 ǰ
WITH CHECK OPTION;

SELECT  *  FROM  ǰ1;


CREATE VIEW	 ǰ2
AS SELECT	 ǰ, , ü
     FROM	 ǰ
WITH CHECK OPTION;

SELECT  *  FROM  ǰ2;


	[ 7-58]
INSERT  INTO      ǰ1     VALUES ('p08', 1000, 'żǰ');

SELECT  *  FROM   ǰ1; 

SELECT  *  FROM   ǰ;


	[ 7-59]
DROP  VIEW  ;